package com.yitao.cms.utils;


import com.yitao.cms.dto.overseasLogistics.OverseasLogisticsInfoDto;
import com.yitao.cms.enums.OverseasLogisticsEnums;
import com.yitao.cms.model.operationUser.OperationUserCmsModel;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Function:
 * Date: 2018/6/2
 *
 * @author skipper
 * @desc
 * @see
 */
public class ExcelExportUtil {
    protected static Logger logger = LoggerFactory.getLogger(ExcelExportUtil.class);

    /**
     * 下载excel模版文件
     *
     * @param dowloadFileUrl 下载路径[从resource/download/的子目录开始]
     * @param request
     * @param response
     * @throws Exception
     */
    public static void downloadExcel(String dowloadFileUrl, HttpServletRequest request,
                                     HttpServletResponse response) throws Exception {
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/vnd.ms-excel"); // 保存为excel
        File file = new File(dowloadFileUrl);
        String name = file.getName();
        String fileName = getFileName(request, name);
        response.setHeader("Content-Disposition", "attachment;" + fileName);
        InputStream in = new FileInputStream(file);
        OutputStream out = response.getOutputStream();
        int len = 1024;
        byte[] b = new byte[len];
        while ((len = in.read(b)) > 0) {
            out.write(b, 0, len);
        }
        in.close();
        out.flush();
        out.close();
    }

    /**
     * 导出Excel
     *
     * @param response
     * @param wb
     * @param
     * @return
     * @throws Exception
     */
    public static boolean doExportExcel(HttpServletRequest request, HttpServletResponse response,
                                        Workbook wb) throws Exception {


        String fileName = "test.xls";// 文件名
        OutputStream out = response.getOutputStream();
        try {
            response.setHeader("Content-disposition", "attachment;filename="
                    + URLEncoder.encode(getFileName(request, fileName), "UTF-8"));
            response.setContentType("application/msexcel;charset=UTF-8");
            wb.write(out);
            return true;
        } finally {
            out.close();
        }
    }

    /**
     * 将文件名转译成不同浏览器能识别的字符编码
     *
     * @param request
     * @param filename
     * @return
     */
    public static String getFileName(HttpServletRequest request, String filename) {
        String userAgent = request.getHeader("user-agent");
        String new_filename, rtn = "filename=\"" + filename + "\"";
        try {
            new_filename = URLEncoder.encode(filename, "UTF-8");

            // 如果没有UA，则默认使用IE的方式进行编码，因为毕竟IE还是占多数的
            rtn = "filename=" + new_filename;
            if (userAgent != null) {
                userAgent = userAgent.toLowerCase();
                // IE浏览器，只能采用URLEncoder编码
                if (userAgent.indexOf("msie") != -1) {
                    rtn = "filename=" + new_filename;
                }
                // Opera浏览器只能采用filename*
                else if (userAgent.indexOf("opera") != -1) {
                    rtn = "filename*=UTF-8''" + new_filename;
                }
                // Safari浏览器，只能采用ISO编码的中文输出
                else if (userAgent.indexOf("safari") != -1) {
//                    rtn = "filename=" + new String(filename.getBytes("UTF-8"), "ISO8859-1");
                    rtn = "filename=" + new_filename;
                }
                // Chrome浏览器，只能采用MimeUtility编码或ISO编码的中文输出
                else if (userAgent.indexOf("applewebkit") != -1) {
                    //new_filename= MimeUtility.encodeText(filename,"UTF-8","B");
//                    rtn = "filename=" + new String(filename.getBytes("UTF-8"), "ISO8859-1");
                    rtn = "filename=" + new_filename;
                }
                // FireFox浏览器，可以使用MimeUtility或filename*或ISO编码的中文输出
                else if (userAgent.indexOf("mozilla") != -1) {
//                    rtn = "filename*=UTF-8''" + new_filename;
                    rtn = "filename=" + new String(filename.getBytes("UTF-8"), "ISO-8859-1");
                }
            }
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }

        return rtn;
    }

    /**
     * 对文件后缀进行判断
     *
     * @param filePath
     * @return
     */
    public static boolean isExcel2003(String filePath) {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    public static boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }

    /**
     * 以字符串类型读取单元格内容
     *
     * @param cell
     * @return
     */
    public static String cellType(Cell cell) {
        if (cell != null) {
            cell.setCellType(Cell.CELL_TYPE_STRING);
            return cell.getStringCellValue();
        }
        return null;//获取数据
    }

    /**
     * 获取日期数据
     *
     * @param cell
     * @return
     */
    public static Date findDate(Cell cell) {
        if (cell != null) {
            return cell.getDateCellValue();
        }
        return null;
    }

    /**
     * 读取的数据转换
     *
     * @param file
     * @return
     */
    public static List<OverseasLogisticsInfoDto> importDate(MultipartFile file, OperationUserCmsModel userSession) {
        Workbook wb = null;
        List<OverseasLogisticsInfoDto> dateList = new ArrayList<>();//主信息
        //获取文件文件内容
        try {
            if (ExcelExportUtil.isExcel2007(file.getOriginalFilename())) {
                wb = new XSSFWorkbook(file.getInputStream());
            } else {
                wb = new HSSFWorkbook(file.getInputStream());
            }
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }
        // List<String> overseaExpressNos = new ArrayList<>();
        //读取文件信息
        Sheet sheet = wb.getSheetAt(0);//获取第一张表
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);//获取第i行数据
            OverseasLogisticsInfoDto overseasLogisticsInfoModel = new OverseasLogisticsInfoDto();
            String overseaExpressNo = ExcelExportUtil.cellType(row.getCell(0)).trim();//获取数据
            if (StringUtils.isEmpty(overseaExpressNo)) {
                break;
            }
            // if (!overseaExpressNos.contains(overseaExpressNo)) {
            overseasLogisticsInfoModel.setOverseaExpressNo(overseaExpressNo);//境外快递单号(必填)
            // overseaExpressNos.add(overseaExpressNo);
            overseasLogisticsInfoModel.setMerchantOrderNo(ExcelExportUtil.cellType(row.getCell(1)));//电商平台订单号(必填)
            overseasLogisticsInfoModel.setRoller(ExcelExportUtil.cellType(row.getCell(2)));//买手姓名
            overseasLogisticsInfoModel.setClientCreateOrderDate(ExcelExportUtil.findDate(row.getCell(3)));//电商平台下单时间
            overseasLogisticsInfoModel.setOrderSource(ExcelExportUtil.cellType(row.getCell(4)));//订单来源
            overseasLogisticsInfoModel.setCustomerRefNo(ExcelExportUtil.cellType(row.getCell(5)));//内部订单号
            if (StringUtils.isBlank(ExcelExportUtil.cellType(row.getCell(6)))) {
                overseasLogisticsInfoModel.setTanksNo(null);//合箱箱号
            } else {
                overseasLogisticsInfoModel.setTanksNo(ExcelExportUtil.cellType(row.getCell(6)));//合箱箱号
            }
            overseasLogisticsInfoModel.setName(ExcelExportUtil.cellType(row.getCell(7)));//商品原始名
            overseasLogisticsInfoModel.setCnyName(ExcelExportUtil.cellType(row.getCell(8)));//商品中文名
            overseasLogisticsInfoModel.setSkuId(ExcelExportUtil.cellType(row.getCell(9)));//电商平台商品id
            overseasLogisticsInfoModel.setUpc(ExcelExportUtil.cellType(row.getCell(10)));//商品UPC码
            overseasLogisticsInfoModel.setCategory(ExcelExportUtil.cellType(row.getCell(11)));//商品分类描述
            overseasLogisticsInfoModel.setBrand(ExcelExportUtil.cellType(row.getCell(12)));//商品品牌
            overseasLogisticsInfoModel.setSpec(ExcelExportUtil.cellType(row.getCell(13)));//商品规格
            overseasLogisticsInfoModel.setPriceUnit(ExcelExportUtil.cellType(row.getCell(14)));//币种
            overseasLogisticsInfoModel.setDeclaredPrice(ExcelExportUtil.cellType(row.getCell(15)));//原币单价
            String count = ExcelExportUtil.cellType(row.getCell(16));
            if (StringUtils.isNotBlank(count)) {
                overseasLogisticsInfoModel.setCount(Integer.valueOf(count));//商品数量
            } else {
                overseasLogisticsInfoModel.setCount(0);//商品数量
            }
            overseasLogisticsInfoModel.setLogisticsProviders(ExcelExportUtil.cellType(row.getCell(17)));//物流服务商
            overseasLogisticsInfoModel.setTransferId(ExcelExportUtil.cellType(row.getCell(18)));//物流服务商单号
            overseasLogisticsInfoModel.setSenderName(ExcelExportUtil.cellType(row.getCell(19)));//发货人姓名
            overseasLogisticsInfoModel.setSenderDetailaddress(ExcelExportUtil.cellType(row.getCell(20)));//国内发件人地址，包括省市区
            overseasLogisticsInfoModel.setSenderMobile(ExcelExportUtil.cellType(row.getCell(21)));//国内发件人电话
            overseasLogisticsInfoModel.setBuyerName(ExcelExportUtil.cellType(row.getCell(22)));//买家(收货人)姓名
            overseasLogisticsInfoModel.setBuyerIdcard(ExcelExportUtil.cellType(row.getCell(23)));//买家(收货人)身份证号码

            overseasLogisticsInfoModel.setBuyerMobile(ExcelExportUtil.cellType(row.getCell(26)));//买家(收货人)手机号码
            overseasLogisticsInfoModel.setBuyerProvince(ExcelExportUtil.cellType(row.getCell(27)));//买家(收货人)省份
            overseasLogisticsInfoModel.setBuyerCity(ExcelExportUtil.cellType(row.getCell(28)));//买家(收货人)城市
            overseasLogisticsInfoModel.setBuyerDistrict(ExcelExportUtil.cellType(row.getCell(29)));//买家(收货人)行政区
            overseasLogisticsInfoModel.setBuyerAdress(ExcelExportUtil.cellType(row.getCell(30)));//买家(收货人)详细地址
            overseasLogisticsInfoModel.setBuyerZipcode(ExcelExportUtil.cellType(row.getCell(31)));//买家邮编
            overseasLogisticsInfoModel.setInlandExpressId(ExcelExportUtil.cellType(row.getCell(32)));//国内快递
            overseasLogisticsInfoModel.setInlandExpressNo(ExcelExportUtil.cellType(row.getCell(33)));//国内运单号
            overseasLogisticsInfoModel.setMessage(ExcelExportUtil.cellType(row.getCell(35)));//备注
            overseasLogisticsInfoModel.setOverseaInTime(ExcelExportUtil.findDate(row.getCell(36)));//转运仓入库时间
            overseasLogisticsInfoModel.setOverseaOutTime(ExcelExportUtil.findDate(row.getCell(37)));//出库时间
            overseasLogisticsInfoModel.setInlandInTime(ExcelExportUtil.findDate(row.getCell(38)));//抵达国内时间
            overseasLogisticsInfoModel.setInlandOutTime(ExcelExportUtil.findDate(row.getCell(39)));//已转国内快递时间
            overseasLogisticsInfoModel.setBuyerSignTime(ExcelExportUtil.findDate(row.getCell(40)));//用户签收时间
            overseasLogisticsInfoModel.setP2Status(OverseasLogisticsEnums.p2Status.FORECAST_NO.getCode());
            overseasLogisticsInfoModel.setP4Status(OverseasLogisticsEnums.p4Status.NO_STOCK.getCode());
            overseasLogisticsInfoModel.setDelete(false);
            overseasLogisticsInfoModel.setCreateBy(userSession.getId());
            overseasLogisticsInfoModel.setCreateTime(new Date());
            // }
            if (overseasLogisticsInfoModel != null && overseasLogisticsInfoModel.getOverseaExpressNo() != null) {
                dateList.add(overseasLogisticsInfoModel);
            }
        }
        return dateList;

    }


    public static Sheet orderImport(MultipartFile file) {
        Workbook wb = null;
        List<OverseasLogisticsInfoDto> dateList = new ArrayList<>();//主信息
        //获取文件文件内容
        try {
            if (ExcelExportUtil.isExcel2007(file.getOriginalFilename())) {
                wb = new XSSFWorkbook(file.getInputStream());
            } else {
                wb = new HSSFWorkbook(file.getInputStream());
            }
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }
        // List<String> overseaExpressNos = new ArrayList<>();
        //读取文件信息
        Sheet sheet = wb.getSheetAt(0);//获取第一张表

        return sheet;
    }

}
